import pymysql
import logging

class DoubanMovieTop250Pipeline:
    def __init__(self):
        # MySQL连接配置
        self.db_config = {
            'host': 'localhost',
            'user': 'root',
            'password': 'your_password',  # 替换为您的MySQL密码
            'db': 'douban_movies',
            'charset': 'utf8mb4',
            'cursorclass': pymysql.cursors.DictCursor
        }
        self.conn = None
        self.cursor = None
    
    def open_spider(self, spider):
        """爬虫启动时连接数据库"""
        try:
            self.conn = pymysql.connect(**self.db_config)
            self.cursor = self.conn.cursor()
            spider.logger.info("数据库连接成功")
        except Exception as e:
            spider.logger.error(f"数据库连接失败: {e}")
            raise
    
    def process_item(self, item, spider):
        """处理每个电影数据项"""
        try:
            # SQL插入语句
            sql = """
            INSERT INTO movies (
                title, rating, rating_num, year, directors, actors, 
                genres, country, language, release_date, runtime, 
                description, poster_url, douban_id, ranking
            ) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
            ON DUPLICATE KEY UPDATE 
                title = VALUES(title),
                rating = VALUES(rating),
                rating_num = VALUES(rating_num),
                year = VALUES(year),
                directors = VALUES(directors),
                actors = VALUES(actors),
                genres = VALUES(genres),
                country = VALUES(country),
                language = VALUES(language),
                release_date = VALUES(release_date),
                runtime = VALUES(runtime),
                description = VALUES(description),
                poster_url = VALUES(poster_url),
                ranking = VALUES(ranking)
            """
            
            # 准备参数
            params = (
                item.get('title', ''),
                item.get('rating', 0.0),
                item.get('rating_num', 0),
                item.get('year', 0),
                item.get('directors', ''),
                item.get('actors', ''),
                item.get('genres', ''),
                item.get('country', ''),
                item.get('language', ''),
                item.get('release_date', ''),
                item.get('runtime', 0),
                item.get('description', ''),
                item.get('poster_url', ''),
                item.get('douban_id', ''),
                item.get('ranking', 0)
            )
            
            # 执行SQL
            self.cursor.execute(sql, params)
            self.conn.commit()
            spider.logger.info(f"已成功存储电影 #{item.get('ranking', 'N/A')}: {item.get('title', 'N/A')}")
            return item
        except Exception as e:
            self.conn.rollback()
            spider.logger.error(f"存储电影数据失败: {e}")
            raise
    
    def close_spider(self, spider):
        """爬虫关闭时关闭数据库连接"""
        if self.cursor:
            self.cursor.close()
        if self.conn:
            self.conn.close()
        spider.logger.info("数据库连接已关闭")